﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Reflection;
using System.Diagnostics;
using System.Data;
using System.Data.Common;
using System.Data.Linq.Mapping;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Collections;
using System.Xml;
using log4net;
using Code.Commen;
using Code.Models;



namespace Code.DAL
{
    /// <summary>
    /// 数据访问层
    /// </summary>
    /// <typeparam name="TableName">指定需要操作的表</typeparam>
    /// <typeparam name="T">由Linq to sql生成的实体，指示需要操作的表</typeparam>
    internal class Table<TableName> where TableName : class
    {
        #region 公共方法

        /// <summary>
        /// 更新由 TableName  指定的表
        /// </summary>
        /// <param name="delWhere">删除的条件,类的属性名要和表的列名一致 无条件则为 null</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>删除的行数</returns>
        public int Delete(params object[] delWhere)
        {
            return Delete<TableName>(delWhere);
        }

        /// <summary>
        /// 删除 TableName  指定的表 的数据
        /// </summary>
        /// <param name="expression">删除的条件</param>
        /// <returns>删除的行数</returns>
        public int Delete<TResult>(Expression<Func<IQueryable<TableName>, IQueryable<TResult>>> expression)
        {
            var item = this.Select<IQueryable<TResult>>(expression).ToArray();

            return Delete(item);
        }

        /// <summary>
        /// 删除 Table  指定的表 的数据
        /// </summary>
        /// <param name="expression">删除的条件</param>
        /// <returns>删除的行数</returns>
        public int Delete<Del,Table>(Expression<Func<IQueryable<Table>, IEnumerable<object>>> expression) where Table : class
        {
            var item = this.Select<Table, IEnumerable<object>>(expression).ToArray();

            return Delete<Del>(item);
        }

        /// <summary>
        /// 执行一个表达式
        /// </summary>
        public int ExecuteExpression(SqlExpression expression)
        {
            int rs = 0;
            StringBuilder sql = this._IsTran ? this._TranSql : new StringBuilder();
            IList<DbParameter> param = this._IsTran ? this._TranParam : new List<DbParameter>();
            sql.Append("\r\n"+PastSqlExpression(expression, param));
            if(!_IsTran)
                rs = ExecuteNonQuery(sql.ToString(), -1, param.ToArray());
            return rs;
        }

        /// <summary>
        /// 更新由 Table 指定的表
        /// </summary>
        /// <param name="delWhere">删除的条件,类的属性名要和表的列名一致 无条件则为 null</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>删除的行数</returns>
        public int Delete<Table>(params object[] delWhere)
        {
            int rs = 0;
            if (delWhere.Length == 0)
                return rs;
            if (!this._IsTran && delWhere.Length > 50)
            {
                int count = (int)Math.Ceiling(delWhere.Length / 50.0);
                for (int i = 0; i < count; i++)
                {
                    rs += Delete<Table>(delWhere.Skip(i * 50).Take(50).ToArray());
                }
                return rs;
            }

            StringBuilder sql = this._IsTran ? this._TranSql : new StringBuilder();
            IList<DbParameter> param = this._IsTran ? this._TranParam : new List<DbParameter>();

            var attrs = typeof(Table).GetCustomAttributes(typeof(System.Data.Linq.Mapping.TableAttribute), false);
            if (attrs.Count() == 0)
            {
                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("Delete<Table> 参数错误:{0}", typeof(Table).FullName));
                throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
            }

            string name = ((System.Data.Linq.Mapping.TableAttribute)attrs[0]).Name;

            if (string.IsNullOrEmpty(name))
            {
                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("Delete<Table> 参数错误:{0}", typeof(Table).FullName));
                throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
            }
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("Delete<{0}> Count:{1}\tIsTran:", typeof(Table).Name, delWhere.Count(), _IsTran));
            sql.Append(BuildDeleteSql(name, param, delWhere));

            if (!this._IsTran)
                rs = ExecuteNonQuery(sql.ToString(),-1, param.ToArray());

            return rs;
        }

        /// <summary>
        /// 新增由 TableName 指定的表
        /// </summary>
        /// <param name="insertItem">新增的集合,类的属性名要和表的列名一致</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>成功的行数</returns>
        public int Insert(params object[] insertItem)
        {
            return Insert<TableName>(insertItem);
        }

        /// <summary>
        /// 新增由 Table 指定的表
        /// </summary>
        /// <param name="insertItem">新增的集合,类的属性名要和表的列名一致</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns></returns>
        public int Insert<Table>(params object[] insertItem)
        {
            int rs = 0;

            if (insertItem.Length == 0)
                return rs;
            if (!this._IsTran && insertItem.Length > 50)
            {
                int count = (int)Math.Ceiling(insertItem.Length / 50.0);
                for (int i = 0; i < count; i++)
                {
                    rs += Insert<Table>(insertItem.Skip(i * 50).Take(50).ToArray());
                }
                return rs;
            }


            StringBuilder sql = this._IsTran ? this._TranSql : new StringBuilder();
            IList<DbParameter> param = this._IsTran ? this._TranParam : new List<DbParameter>();

            var attrs = typeof(Table).GetCustomAttributes(typeof(TableAttribute), false);
            if (attrs.Count() == 0)
            {
                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("Delete<Table> 参数错误:{0}", typeof(Table).FullName));
                throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
            }

            string name = ((TableAttribute)attrs[0]).Name;

            if (string.IsNullOrEmpty(name))
            {
                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("Delete<Table> 参数错误:{0}", typeof(Table).FullName));
                throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
            }

            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("Insert<{0}> Count:{1}\tIsTran:", typeof(Table).Name, insertItem.Count(), _IsTran));
            sql.Append(BuildInsertSql(name, param, insertItem));

            if (!this._IsTran)
                rs = ExecuteNonQuery(sql.ToString(),-1, param.ToArray());

            return rs;
        }

        #region select
        /// <summary>
        /// 查询由 TableName 指定的表
        /// </summary>
        /// <param name="expression">查询表达式</param>
        /// <exception cref="System.ArgumentNullException" />
        public TResult Select<TResult>(Expression<Func<IQueryable<TableName>, TResult>> expression)
        {
            return Select<TableName, TResult>(expression);

        }

        /// <summary>
        /// 查询由 Table 指定的表
        /// </summary>
        /// <exception cref="System.ArgumentNullException" />
        /// <param name="expression">查询表达式</param>
        public TResult Select<Table, TResult>(Expression<Func<IQueryable<Table>, TResult>> expression) where Table : class
        {
            if (expression == null)
                throw new System.ArgumentNullException("expression");

            //bool conopen = DB.DatabaseExists();
            //if (!conopen)
            //    throw new System.Exception("数据库连接出错");

            var source = DB.GetTable<Table>();

            var ex = expression.Compile();


            var rs = ex(source);

            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("Select<{0}> rs:{1}\t", typeof(Table).Name, rs));

            return rs;

        }


        /// <summary>
        /// 获取对 TEntity 指定表的查询
        /// </summary>
        /// <typeparam name="TEntity">返回对象的类型</typeparam>
        public System.Data.Linq.Table<TEntity> GetTable<TEntity>() where TEntity : class { return DB.GetTable<TEntity>(); }
        #endregion

        /// <summary>
        /// 更新由 TableName  指定的表的数据
        /// </summary>
        /// <param name="update">要更新的属性,类的属性名要和表的列名一致</param>
        /// <param name="where">做为筛选的条件,类的属性名要和表的列名一致 无条件则为 null</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>更新的行数</returns>
        public int Update(object update, object where)
        {
            return Update<TableName>(new NameValue<object, object>() { { update, where } });
        }

        /// <summary>
        /// 更新由 Table 指定的表的数据
        /// </summary>
        /// <param name="update">要更新的属性,类的属性名要和表的列名一致</param>
        /// <param name="where">做为筛选的条件,类的属性名要和表的列名一致 无条件则为 null</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>更新的行数</returns>
        //public int Update<Table>(object update, object where)
        //{
        //    return Update<Table>(new Dictionary<object, object>() { { update, where } });
        //}

        /// <summary>
        /// 更新由 TableName  指定的表的数据
        /// </summary>
        /// <param name="updateItem">由键值队组成的集合，每一个键值队代表一个更新操作，
        /// Key为要更新的内容,类的属性名要和表的列名一致
        /// Value做为 Where 条件的类,类的属性名要和表的列名一致 无条件则为 null，
        /// </param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>更新的行数</returns>
        //public int Update(IDictionary<object, object> updateItem)
        //{
        //    return Update<TableName>(updateItem);
        //}

        /// <summary>
        /// 更新由 Table  指定的表的数据
        /// </summary>
        /// <param name="updateItem">由键值队组成的集合，每一个键值队代表一个更新操作，
        /// Key为要更新的内容,类的属性名要和表的列名一致
        /// Value做为 Where 条件的类,类的属性名要和表的列名一致 无条件则为 null，
        /// </param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>更新的行数</returns>
        //public int Update<Table>(IDictionary<object, object> updateItem)
        //{
        //    int rs = 0;
        //    if (updateItem.Count == 0)
        //        return rs;

        //    StringBuilder sql = this._IsTran ? this._TranSql : new StringBuilder();
        //    IList<DbParameter> param = this._IsTran ? this._TranParam : new List<DbParameter>();
        //    var attrs = typeof(Table).GetCustomAttributes(typeof(System.Data.Linq.Mapping.TableAttribute), false);
        //    if (attrs.Count() == 0)
        //    {
        //        if (Log.IsDebugEnabled)
        //            Log.Debug(string.Format("Update<Table> 参数错误:{0}", typeof(Table).FullName));
        //        throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
        //    }

        //    string name = ((System.Data.Linq.Mapping.TableAttribute)attrs[0]).Name;

        //    if (string.IsNullOrEmpty(name))
        //    {
        //        if (Log.IsDebugEnabled)
        //            Log.Debug(string.Format("Update<Table> 参数错误:{0}", typeof(Table).FullName));
        //        throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
        //    }

        //    if (Log.IsDebugEnabled)
        //        Log.Debug(string.Format("Update<{0}>(IDictionary<object, object>) Count:{1}\tIsTran:", typeof(Table).Name, updateItem.Count(), _IsTran));
        //    sql.Append(BuildUpdateSql(name, param, updateItem.Select(p => new NameValueItem<object, object> { Key = p.Key, Value = p.Value })));

        //    if (!this._IsTran)
        //        rs = ExecuteNonQuery(sql.ToString(),-1, param.ToArray());

        //    return rs;
        //}

        /// <summary>
        /// 更新由 Table  指定的表的数据
        /// </summary>
        /// <param name="updateItem">由键值队组成的集合，每一个键值队代表一个更新操作，
        /// Key为要更新的内容,类的属性名要和表的列名一致
        /// Value做为 Where 条件的类,类的属性名要和表的列名一致 无条件则为 null，
        /// </param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>更新的行数</returns>
        public int Update<Table>(NameValue<object, object> updateItem)
        {
            int rs = 0;
            if (updateItem.Count == 0)
                return rs;

            StringBuilder sql = this._IsTran ? this._TranSql : new StringBuilder();
            IList<DbParameter> param = this._IsTran ? this._TranParam : new List<DbParameter>();
            var attrs = typeof(Table).GetCustomAttributes(typeof(System.Data.Linq.Mapping.TableAttribute), false);
            if (attrs.Count() == 0)
            {
                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("Update<Table> 参数错误:{0}", typeof(Table).FullName));
                throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
            }

            string name = ((System.Data.Linq.Mapping.TableAttribute)attrs[0]).Name;

            if (string.IsNullOrEmpty(name))
            {
                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("Update<Table> 参数错误:{0}", typeof(Table).FullName));
                throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
            }

            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("Update<{0}>(NameValue<object, object>) Count:{1}\tIsTran:", typeof(Table).Name, updateItem.Count(), _IsTran));
            sql.Append(BuildUpdateSql(name, param, updateItem));

            if (!this._IsTran)
                rs = ExecuteNonQuery(sql.ToString(), -1, param.ToArray());

            return rs;
        }


        /// <summary>
        /// 更新由 Table  指定的表的数据
        /// </summary>
        /// <param name="updateItem">由键值队组成的集合，每一个键值队代表一个更新操作，
        /// Key为要更新的内容,类的属性名要和表的列名一致
        /// Value做为 Where 条件的类,类的属性名要和表的列名一致 无条件则为 null，
        /// </param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>更新的行数</returns>
        public int Update(NameValue<object, object> updateItem)
        {
            return Update<TableName>(updateItem);
        }

        /// <summary>
        /// 更新由 TableName  指定的表的数据
        /// </summary>
        /// <param name="updates">由键值队组成的集合，每一个键值队代表一个更新表的操作，
        /// Key做为 由Linq to sql生成的实体，指示需要操作的表
        /// Value为要更新的数据 {
        ///     由键值队组成的集合，每一个键值队代表一个更新操作，
        ///     Key为要更新的内容 条件的类,类的属性名要和表的列名一致
        ///     Value做为 Where 条件的类,类的属性名要和表的列名一致 无条件则为 null，
        /// }
        /// </param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>更新的行数</returns>
        public int Update(IDictionary<Type, NameValue<object, object>> updates)
        {
            int rs = 0;
            if (updates.Count == 0)
                return rs;

            StringBuilder sql = this._IsTran ? this._TranSql : new StringBuilder();
            IList<DbParameter> param = this._IsTran ? this._TranParam : new List<DbParameter>();

            foreach (var table in updates)
            {
                var attrs = table.Key.GetCustomAttributes(typeof(System.Data.Linq.Mapping.TableAttribute), false);
                if (attrs.Count() == 0)
                {
                    if (Log.IsDebugEnabled)
                        Log.Debug(string.Format("Update<Table> 参数错误:{0}", table.Key.FullName));
                    throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
                }

                string name = ((System.Data.Linq.Mapping.TableAttribute)attrs[0]).Name;

                if (string.IsNullOrEmpty(name))
                {
                    if (Log.IsDebugEnabled)
                        Log.Debug(string.Format("Update<Table> 参数错误:{0}", table.Key.FullName));
                    throw new System.ArgumentOutOfRangeException("Type must be use Linq to sql Entites");
                }

                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("Update<{0}>(IDictionary<Type, Dictionary<object, object>>) Count:{1}\tIsTran:", table.Key.Name, table.Value.Count(), _IsTran));
                sql.Append(BuildUpdateSql(name, param, table.Value));
            }

            if (!this._IsTran)
                rs = ExecuteNonQuery(sql.ToString(),-1, param.ToArray());

            return rs;
        }

        /// <summary>
        /// 指示 后续的 增、册、改 操作都将视为一个事务，直到显示的调用 ExecuteTransaction() 方法才提交执行
        /// 或调用 CancelTransaction() 取消事务
        /// <exception cref="System.InvalidOperationException"/>
        /// </summary>
        public void BeginTransaction()
        {
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("BeginTransaction() \tIsTran:", _IsTran));

            if (this._IsTran)
            {
                throw new System.InvalidOperationException("已有一个事务在执行");
            }
            else
            {
                this._TranSql = new StringBuilder();
                this._TranParam = new List<DbParameter>();
                this._IsTran = true;
            }
        }

        /// <summary>
        /// 取消事务，取消至 BeginTransaction() 
        /// </summary>
        public void CancelTransaction()
        {
            this._IsTran = false;
            this._TranParam = null;
            this._TranSql = null;
        }

        /// <summary>
        /// 开始提交由最近的一次BeginTransaction()之后的操作 反回:受影响的行数 -1 数据库错误，-2 已回滚事务
        /// </summary>
        /// <exception cref="System.InvalidOperationException"/>
        /// <returns>更新的行数</returns>
        public int ExecuteTransaction()
        {
            return this.CommitWithCount(-1);
        }

        public void JoinTran<NewTableName>(Table<NewTableName> newTran)where NewTableName:class
        {
            if (newTran == null)
                throw new ArgumentException();
            if (!newTran._IsTran)
                throw new ArgumentException("尚未开启事务，不能执行此操作");
            if (!this._IsTran)
                throw new System.InvalidOperationException("必需先显示的调用BeginTransaction()开始一个事务后才能执行此操作");
            if (_TranParam == null)
                _TranParam = new List<DbParameter>();
            if (_TranSql == null)
                _TranSql = new StringBuilder();
            if (newTran._TranParam != null&&newTran._TranSql != null)
            {
                var count = this._TranParam.Count;
                var count2 = newTran._TranParam.Count;
                if (count >= count2)
                {
                    var sql = newTran._TranSql.ToString();
                    for (int i = count2 - 1; i >= 0; i--)
                    {
                        var parameterName = "@P_" + (count + i);
                        sql = sql.Replace("@P_" + i + " ", parameterName + " ");
                        newTran._TranParam[i].ParameterName = parameterName;
                        this._TranParam.Add(newTran._TranParam[i]);
                    }
                    this._TranSql.Append(sql);
                }
                else
                {
                    var sql = this._TranSql.ToString();
                    for (int i = count-1; i >= 0; i--)
                    {
                        var parameterName = "@P_" + (count2 + i);
                        sql = sql.Replace("@P_" + i + " ", parameterName + " ");
                        this._TranParam[i].ParameterName = parameterName;
                        newTran._TranParam.Add(this._TranParam[i]);
                    }
                    this._TranSql = newTran._TranSql;
                    this._TranSql.Insert(0, sql);
                    this._TranParam = newTran._TranParam;
                }
            }
            else if (newTran._TranSql != null)
                this._TranSql.Append(newTran._TranSql.ToString());

        }

        /// <summary>
        /// 开始提交由最近的一次BeginTransaction()之后的操作 反回:受影响的行数 -1 数据库错误，-2 已回滚事务
        /// </summary>
        /// <param name="count">受影响的行数，如果不等于此预计的行数，则回滚,此参数小于零则不处理受景响的行数</param>
        /// <exception cref="System.InvalidOperationException"/>
        /// <returns>更新的行数</returns>
        public int CommitWithCount(int count)
        {
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("ExecuteTransaction()\tIsTran:", _IsTran));

            if (!this._IsTran)
                throw new System.InvalidOperationException("必需先显示的调用BeginTransaction()开始一个事务后才能执行此操作");
            try
            {
                return this.ExecuteNonQuery(_TranSql.ToString(), count, _TranParam.ToArray());
            }
            catch
            {
                return -1;
            }
            finally
            {

                this._IsTran = false;
                this._TranParam = null;
                this._TranSql = null;
            }
        }

        /// <summary>
        /// 数据库
        /// </summary>
        public DBContext DB { get; set; }
        public Table()
        {
            DB = DBHelper.DBContext;
        }
        public Table(DBContext context)
        {
            DB = context;
        }
        #endregion

        #region 私有成员

        /// <summary>
        /// 执行 Sql 语句 反回:受影响的行数 -1 数据库错误，-2 已回滚事务
        /// </summary>
        /// <param name="sql">Sql 语句</param>
        /// <param name="count">受影响的行数，如果不等于此预计的行数，则回滚,此参数小于零则不处理受景响的行数</param>
        /// <param name="param">参数列表</param>
        /// <returns>受影响的行数 -1 数据库错误，-2 已回滚事务</returns>
        private int ExecuteNonQuery(string sql,int count, params System.Data.Common.DbParameter[] param)
        {
            if (string.IsNullOrEmpty(sql))
                return 0;
            int rs = -1;

            var con = DBHelper.Connection;

            var cmd = con.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(param.ToArray());

            try
            {
                con.Open();
                cmd.Transaction = con.BeginTransaction();
                rs = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (Log.IsDebugEnabled)
                    Log.Debug(string.Format("ExecuteNonQuery() Error:{0},sql{1}", e.Message, sql), e);

                if (cmd.Transaction != null)
                    cmd.Transaction.Rollback();
            }
            finally
            {
                if (cmd.Transaction != null && rs > -1)
                {
                    if (count > 0 && count == rs)
                        cmd.Transaction.Commit();
                    else if (count > 0)
                    {
                        rs = -2;
                        cmd.Transaction.Rollback();
                    }
                    else
                        cmd.Transaction.Commit();
                }
                    
                if (con != null)
                    con.Dispose();
                if (param != null)
                    param = null;
            }
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("ExecuteNonQuery() rs:{0}", rs));
            return rs;
        }

        /// <summary>
        /// 用于生成 更新操作 的SQL语句
        /// </summary>
        /// <param name="tableName">要新增的表名</param>
        /// <param name="param">参数对象</param>
        /// <param name="insertItem">要新增的内容</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>生成的SQL语句</returns>
        private string BuildInsertSql(string tableName, IList<DbParameter> param, params object[] insertItem)
        {
            StringBuilder str = new StringBuilder("");

            tableName = FormatTableName(tableName);

            foreach (var item in insertItem)
            {
                StringBuilder cols = new StringBuilder();
                StringBuilder value = new StringBuilder();

                var collumns = PastColumn(item);

                foreach (var col in collumns)
                {
                    object val = col.Value;

                    cols.Append(", [" + col.Key + "]");
                    if (val == null)
                    {
                        value.Append(", NULL");
                    }
                    else
                    {
                        if (val is SqlExpression)
                        {
                            var temp = val as SqlExpression;
                            value.Append(",(" + PastSqlExpression(temp,param) + ")");
                        }
                        else
                        {
                            value.Append(", " + GetParamName(param,val));
                        }
                    }
                }

                str.Append("\r\n INSERT INTO " + tableName + " (" + cols.ToString(1, cols.Length - 1) + ") VALUES (" + value.ToString(1, value.Length - 1) + ")");
            }
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("BuildInsertSql sql:{0}", str.ToString()));
            return str.ToString();
        }

        /// <summary>
        /// 用于生成 删除操作 的SQL语句
        /// </summary>
        /// <param name="tableName">要删除的表名</param>
        /// <param name="param">参数对象</param>
        /// <param name="delItem">删除的条件</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>生成的SQL语句</returns>
        private string BuildDeleteSql(string tableName, IList<DbParameter> param, params object[] delItem)
        {
            if (string.IsNullOrEmpty(tableName) || param == null)
            {
                throw new System.ArgumentOutOfRangeException("tableName and param is not null");
            }

            tableName = FormatTableName(tableName);

            StringBuilder str = new StringBuilder("");

            foreach (var item in delItem)
            {
                var collumns = PastColumn(item);

                if (collumns == null || collumns.Count == 0)
                    continue;

                str.Append("\r\n DELETE FROM " + tableName + " WHERE 1=1 ");

                foreach (var col in collumns)
                {
                    ComparValue comparvalue;
                    object val = col.Value;

                    if (!(val is ComparValue))
                        comparvalue = new ComparValue() { Value = val, Compar = val == null ? Comparison.Is : Comparison.Equal };
                    else
                        comparvalue = (ComparValue)val;

                    str.Append(BuildWhereExpression(col.Key, param, comparvalue));
                }
            }
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("BuildDeleteSql sql:{0}", str.ToString()));

            return str.ToString();
        }

        /// <summary>
        /// 用于生成 更新操作 的SQL语句
        /// </summary>
        /// <param name="tableName">要更新的表</param>
        /// <param name="param">参数对象</param>
        /// <param name="updateItem">参数对象</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>生成的SQL语句</returns>
        private string BuildUpdateSql(string tableName, IList<DbParameter> param, IEnumerable<NameValueItem<object, object>> updateItem)
        {
            if (string.IsNullOrEmpty(tableName) || param == null || updateItem == null)
            {
                throw new System.ArgumentOutOfRangeException("tableName , param and updateItem is not null");
            }

            StringBuilder str = new StringBuilder("");

            tableName = FormatTableName(tableName);

            foreach (var item in updateItem)
            {
                var columns = PastColumn(item.Key);
                if (columns == null || columns.Count == 0)
                    continue;

                str.Append("\r\n UPDATE " + tableName + " SET ");

                foreach (var col in columns)
                {
                    if (col == null)
                    {
                        str.Append("[" + col.Key + "] = NULL , ");
                    }
                    else
                    {
                        str.Append(BuildSetExpression(col.Key, param, col.Value) + ", ");
                    }
                }

                str.Remove(str.Length - 2, 2);

                columns = PastColumn(item.Value);
                if (columns == null || columns.Count == 0)
                    continue;

                var collumns = PastColumn(item.Value);
                if (collumns.Count == 0)
                    continue;

                //Where条件部份
                str.Append(" WHERE 1 = 1 ");

                foreach (var col in collumns)
                {
                    ComparValue comparvalue;
                    object val = col.Value;

                    if (!(val is ComparValue))
                        comparvalue = new ComparValue() { Value = val, Compar = val == null ? Comparison.Is : Comparison.Equal };
                    else
                        comparvalue = (ComparValue)val;

                    str.Append(BuildWhereExpression(col.Key, param, comparvalue));
                }
            }
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("BuildUpdateSql sql:{0}", str.ToString()));

            return str.ToString();
        }



        /// <summary>
        /// 用于生成 Where 部分的 条件比较表达式
        /// </summary>
        /// <param name="colName">列名</param>
        /// <param name="param">参数对象</param>
        /// <param name="value">比较的值</param>
        /// <exception cref="System.ArgumentOutOfRangeException" />
        /// <returns>SQL语句</returns>
        private string BuildWhereExpression(string colName, IList<DbParameter> param, ComparValue value)
        {
            StringBuilder str = new StringBuilder();
            if (value.Compar.IsRange)
            {
                if (value.Compar == Comparison.In || value.Compar == Comparison.NotIn)
                {
                    if (value.Value is SqlExpression)
                    {
                        var tmp = (SqlExpression)value.Value;
                        str.Append("AND [" + colName + "] " + value.Compar + " (" + PastSqlExpression(tmp,param) + ") ");
                    }
                    else
                    {
                        var items = value.Value as IEnumerable;
                        if (items == null)
                            throw new ArgumentOutOfRangeException("Compar.Value is not IEnumerable<object>");

                        str.Append("AND [" + colName + "] " + value.Compar + " (");
                        List<object> t = new List<object>();
                        foreach (var item in items)
                            t.Add(item);
                        var names = t.Select(p => p == null ? "NULL" : GetParamName(param, p)).ToArray();
                        str.Append(string.Join(", ", names));
                        str.Append(") ");
                    }
                }
                else if (value.Compar == Comparison.Between || value.Compar == Comparison.NotBetween)
                {
                    if (value.Value == null || value.ValueEnd == null)
                        throw new ArgumentOutOfRangeException("Compar.Value and Compar.ValueEnd can not have null");

                    if (value.Value is SqlExpression)
                    {
                        var tmp = (SqlExpression)value.Value;
                        str.Append("AND [" + colName + "] " + value.Compar + " (" + PastSqlExpression(tmp,param) + ") ");
                    }
                    else
                    { 
                        str.Append("AND [" + colName + "] " + value.Compar + " " + GetParamName(param,value.Value) + " ");                    
                    }
                    if (value.ValueEnd is SqlExpression)
                    {
                        var tmp = (SqlExpression)value.Value;
                        str.Append("AND [" + colName + "] " + value.Compar + " (" + PastSqlExpression(tmp, param) + ") ");
                    }
                    else
                    {
                        str.Append("AND " + GetParamName(param, value) + " ");
                    }
                }
            }
            else
            {
                if(value.Compar==Comparison.True)
                {
                    str.Append("AND " + value.Compar + " ");
                }
                else if (value.Compar == Comparison.Sql)
                {
                    if ("NULL".Equals(colName.ToUpper()))
                    {
                        if (value.Value is SqlExpression)
                        {
                            var tmp = (SqlExpression)value.Value;
                            str.Append(string.Format("AND {1} ", colName, PastSqlExpression(tmp, param)));
                        }
                        else
                        {
                            str.Append(string.Format("AND {1} ", colName, value.Value.ToString()));
                        }
                    }
                    else
                    {
                        if (value.Value is SqlExpression)
                        {
                            var tmp = (SqlExpression)value.Value;
                            str.Append(string.Format("AND [{0}] {1} ", colName, PastSqlExpression(tmp, param)));
                        }
                        else
                        {
                            str.Append(string.Format("AND [{0}] {1} ", colName, value.Value.ToString()));
                        }
                    }
                }
                else if (value.Compar == Comparison.Is || value.Compar == Comparison.IsNot)
                {
                    if (value.Value != null)
                        throw new ArgumentOutOfRangeException("IS just use compar null");
                    else
                        str.Append("AND [" + colName + "] " + value.Compar + " NULL ");
                }
                else
                {
                    if (value.Value == null)
                        throw new ArgumentOutOfRangeException("compar null must be use IS");
                    else
                    {
                        if (value.Value is SqlExpression)
                        {
                            var tmp = (SqlExpression)value.Value;
                            if ("NULL".Equals(colName.ToUpper()))
                                str.Append(PastSqlExpression(tmp,param));
                            else
                                str.Append("AND [" + colName + "] " + value.Compar + " (" + PastSqlExpression(tmp, param) + ") ");
                        }
                        else
                        {
                            str.Append("AND [" + colName + "] " + value.Compar + " " + GetParamName(param,value.Value) + " ");
                        }
                    }
                }

            }
            if (Log.IsDebugEnabled)
                Log.Debug(string.Format("BuildSubExpression sql:{0}", str.ToString()));

            return str.ToString();
        }

        private string BuildSetExpression(string colName, IList<DbParameter> param, object value)
        {
            string str;
            if (string.IsNullOrEmpty(colName))
                throw new ArgumentNullException("colName");
            if (param == null)
                throw new ArgumentNullException("param");

            if (value == null)
            {
                str = string.Format(" [{0}] = NULL ",colName);
            }
            else if (value is SqlExpression)
            {
                var tmp = (SqlExpression)value;
                if ("NULL".Equals(colName.ToUpper()))
                    str = PastSqlExpression(tmp, param);
                else
                    str = string.Format(" [{0}] = ({1})", colName, PastSqlExpression(tmp, param));
            }
            else if (value is OpteratorValue)
            {
                var tmp = (OpteratorValue)value;

                str = tmp.ToOpteratorString(colName, GetParamName(param, tmp.Value));
            }
            else
            {
                str = string.Format(" [{0}] = {1}", colName, GetParamName(param,value));
            }

            return str;
        }

        /// <summary>
        /// 添加参数 并返回参数名
        /// </summary>
        public string GetParamName(IList<DbParameter> param, object value)
        {
            var item = param.FirstOrDefault(p => p.Value.Equals(value));
            if (item == null)
            {
                item = new SqlParameter("@P_" + param.Count, value);
                param.Add(item);
            }

            return item.ParameterName;
        }

        /// <summary>
        /// 解析Sql表达式，并返回SQL语句
        /// </summary>
        private string PastSqlExpression(SqlExpression ex,IList<System.Data.Common.DbParameter> param)
        {
            List<string> names = new List<string>();
            for (int j = 0; j < ex.Params.Length; j++)
            {
                var val=ex.Params[j];
                if (val == null)
                    names.Add("NULL");
                else
                    names.Add(GetParamName(param, val));
            }
            return string.Format(ex.Expression, names.ToArray());
        }

        /// <summary>
        /// 解析参数对象
        /// </summary>
        /// <param name="obj">可为(匿名类，NameValue对象,Dictionary对象)</param>
        private NameValue<string, object> PastColumn(object obj)
        {
            NameValue<string, object> rs = new NameValue<string, object>();

            if (obj == null)
                return null;

            if (obj is NameValue<string, object>)
            {
                rs = obj as NameValue<string, object>;
            }
            else if (obj is Dictionary<string, object>)
            {
                var collection = obj as Dictionary<string, object>;
                foreach (var item in collection)
                {
                    rs.Add(item.Key, item.Value);
                }
            }
            else
            {
                var properties = TypeDescriptor.GetProperties(obj);

                if (properties.Count > 0)
                {
                    foreach (PropertyDescriptor property in properties)
                    {
                        rs.Add(property.Name, property.GetValue(obj));
                    }
                }

            }

            return rs;
        }

        /// <summary>
        /// 格式化成标准的数据库表名
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private string FormatTableName(string tableName)
        {
            //if (string.IsNullOrEmpty(tableName))
            //    throw new ArgumentNullException("tableName");
            //if (tableName[0] == '[')
            //    return tableName;
            //string[] split = tableName.Split('.');
            //tableName = string.Format("[{0}]", split[0]);
            //string table = string.Join("", split.Skip(1).ToArray());
            //tableName += table == "" ? "" : string.Format(".[{0}]", table);

            return tableName;
        }
        
        /// <summary>
        /// 用于生成SQL语句
        /// </summary>
        private StringBuilder _TranSql;

        /// <summary>
        /// 执行SQL使用的参数集合
        /// </summary>
        private IList<DbParameter> _TranParam;

        /// <summary>
        /// 指示当前的操作状态是否为一个事务
        /// </summary>
        private bool _IsTran;

        /// <summary>
        /// 日志记录对象
        /// </summary>
        private static ILog Log = Code.Commen.Log.GetLog("Code.DAL.Table." + typeof(TableName).Name);

        #endregion
    }
}

